package com.example.excelimport.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @ClassName: PoiUtil
 * @DESCRIPT: TODO
 * @AUTHOR: cuizhichao
 * @DATA: 2020/1/14 10:49 AM
 **/
public class PoiUtil {
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";

    /**
     * 读入excel文件，解析后返回
     * @param beginRow 列名开始行
     * @param file
     * @throws IOException
     */
    public static Map<String, Object> readExcel(String path, int beginRow, MultipartFile file) throws IOException {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        Map<String, Object> result = new HashMap<>();
        List<Map<String, String>> parsList = null;
        List<String> colNames = new ArrayList<>();
        Map<Object,Object> map = new HashMap<>();
        String[] cells = null;

        if (workbook != null) {
            parsList = ParsXml.getConfig(path);
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null) {
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();

                //获取表格列名
                for (int rowNum = beginRow; rowNum <= beginRow; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    cells = new String[row.getPhysicalNumberOfCells()];
                    for(int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++){
                        Cell cell = row.getCell(cellNum);
                        colNames.add(getCellValue(cell,""));
                    }
                }

                List<Map<String,Object>> rowDataList = new ArrayList<>();

                //循环除了第一行的所有行
                for (int rowNum = firstRowNum + beginRow+1; rowNum <= lastRowNum; rowNum++) {
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    cells = new String[row.getPhysicalNumberOfCells()];
                    //循环当前列
                    Map<String,Object> dateMap = new LinkedHashMap<>();
                    for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        //校验数据合法性
                        map = getRowStandardMsg(parsList,colNames,map,cell,cellNum,rowNum);

                        if(map.size() == 0){
                            dateMap.put(colNames.get(cellNum),getCellValue(cell,""));
                            //数据为空添加默认值
                            for (Map<String,String> regMap:parsList) {
                                //数据为空为数据赋默认值
                                if((cell == null || getCellValue(cell,"").equals(""))
                                        && regMap.get("name").equals(colNames.get(cellNum))){
                                    dateMap.put(colNames.get(cellNum),getCellValue(cell,regMap.get("value")));
                                }
                            }
                        }
                    }
                    rowDataList.add(dateMap);
                }
                if (map.size() > 0) {
                    result.put("validate", false);
                    result.put("data", map);
                    result.put("group",parsList.get(0).get("group"));
                    return result;
                }
                result.put("validate", true);
                result.put("data", rowDataList);
            }
        }
        return result;
    }

    /**
     * 检查文件
     * @param file
     * @throws IOException
     */
    public static void checkFile(MultipartFile file) throws IOException {
        //判断文件是否存在
        if (null == file) {
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if (!fileName.endsWith(xls) && !fileName.endsWith(xlsx)) {
            throw new IOException(fileName + "不是excel文件");
        }
    }

    /**
     * 获取03/07版本workbook
     * @param file
     * @return
     */
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(xls)) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(xlsx)) {
                //2007 及2007以上
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
        }
        return workbook;
    }


    /**
     *获取CELL值
     */
    public static String getCellValue(Cell cell,String value) {
        String cellValue = "";
        if (cell == null) {
            return value;
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double dateValue = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(dateValue);
                        cellValue = sdf.format(date);
                        return cellValue;
                    }else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                        return cellValue;
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    }finally{
                        sdf = null;
                    }
                }  else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                    return cellValue;
                }
            case Cell.CELL_TYPE_STRING: //字符串
                if(cell.getStringCellValue().equals("")){
                    cellValue = value;
                    break;
                }
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                try {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = value;
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


    /**
     *
     * @param parsList xml解析内容
     * @param colNames 列名数组
     * @param cell  CEll对象
     * @param cellNum 列数
     * @param rowNum 行数
     * @return
     */
    public static Map<Object,Object> getRowStandardMsg(List<Map<String,String>> parsList,List<String> colNames,
                                                Map<Object,Object> map,Cell cell,int cellNum,int rowNum){
        if(parsList == null){
            return map;
        }
        for (Map<String,String> parsMap:parsList) {
            //当数据为空且默认值不为空时证明可以为空，直接返回
            if ((cell == null || getCellValue(cell,"").equals(""))
                    && parsMap.get("value") != null){
                return map;
            }
            String reg = parsMap.get("regContent");
            if (reg != null && !reg.equals("")
                    && parsMap.get("name").equals(colNames.get(cellNum))
                    && !getCellValue(cell,"").matches(reg)) {
                switch (parsMap.get("group")){
                    //此处rowNum+1是要符合excel包括表头在内的行数
                    case "row" : map = appendMap(map,rowNum+1,parsMap.get("name")+parsMap.get("errorMsg"));break;
                    case "col" : map = appendMap(map,parsMap.get("name")+"列"+parsMap.get("errorMsg"),rowNum+1);break;
                    default:map = appendMap(map,rowNum+1,parsMap.get("name")+parsMap.get("errorMsg"));break;
                }
            }
        }
        return map;
    }

    /**
     * 统一信息
     * @param map
     * @param key 键
     * @param value 值
     * @return
     */
    public static Map<Object,Object> appendMap(Map<Object,Object> map,Object key,Object value){
        if(map.containsKey(key)){
            map.put(key,map.get(key)+","+value.toString());
        }else{
            map.put(key,value);
        };
        return map;
    }

    /**
     * 根据行或列组装返回错误文本
     * @param map
     * @return
     */
    public static String getErrorMsg(Map<String,Object> map){
        StringBuilder errors = new StringBuilder();
        Map<String,Object> errorMap = (Map<String, Object>) map.get("data");
        Set<Map.Entry<String,Object>> entrySet = errorMap.entrySet();
        int index = 1;
        for (Map.Entry<String,Object> e: entrySet) {
            if(map.get("group").equals("row")){
                errors.append(String.format("%d:第%s行%s",index,e.getKey(),e.getValue())).append("</br>");
            }else {
                errors.append(String.format("%d:%s(第%s行)",index,e.getKey(),e.getValue())).append("</br>");
            }
            index++;
        }
        return errors.toString();
    }

}
